﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {        

        #region 人才招聘
        /// <summary>
        /// 发布人才招聘
        /// </summary>
        /// <returns></returns>
        public int InsertJobs(JobsInfo jobsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,jobsInfo.UserUid),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsInfo.JobsId),
                                      DbHelper.MakeInParam("@companyname",(DbType)SqlDbType.NChar,40,jobsInfo.CompanyName),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NChar,20,jobsInfo.JobsTitle),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,jobsInfo.Address),
                                      DbHelper.MakeInParam("@contact",(DbType)SqlDbType.NChar,20,jobsInfo.Contact),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,jobsInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,30,jobsInfo.Email),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,1500,jobsInfo.JobsContent),
                                      DbHelper.MakeInParam("@number",(DbType)SqlDbType.Int,int.MaxValue,jobsInfo.Number),
                                      DbHelper.MakeInParam("@paytreatment",(DbType)SqlDbType.NChar,15,jobsInfo.PayTreatment),                                     
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NVarChar,60,jobsInfo.Phone),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.Int,4,jobsInfo.Sex),
                                      DbHelper.MakeInParam("@welfare",(DbType)SqlDbType.NVarChar,50,jobsInfo.Welfare),
                                      DbHelper.MakeInParam("@workplace",(DbType)SqlDbType.NChar,40,jobsInfo.Workplace),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,jobsInfo.Audit),
                                      DbHelper.MakeInParam("@maturity",(DbType)SqlDbType.NVarChar,jobsInfo.Maturity.Length,jobsInfo.Maturity),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.NVarChar,jobsInfo.Published.Length,jobsInfo.Published),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,jobsInfo.Date.Length,jobsInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}jobs] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([userid],[jobsid] ,[companyname],[title],[address],[contact],[education],[email],[content],[number],[paytreatment],[phone],[sex],[welfare],[workplace],[audit],[published],[maturity],[date])");
            commantText.Append("VALUES(@userid,@jobsid,@companyname,@title,@address,@contact,@education,@email,@content,@number,@paytreatment,@phone,@sex,@welfare,@workplace,@audit,@published,@maturity,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和产品ID更新人才招聘信息
        /// </summary>
        /// <param name="jobsInfo"></param>
        /// <returns></returns>
        public int UpdateJobsByJobsIdAndUserID(JobsInfo jobsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,jobsInfo.UserUid),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsInfo.JobsId),
                                      DbHelper.MakeInParam("@companyname",(DbType)SqlDbType.NChar,40,jobsInfo.CompanyName),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NChar,20,jobsInfo.JobsTitle),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,jobsInfo.Address),
                                      DbHelper.MakeInParam("@contact",(DbType)SqlDbType.NChar,20,jobsInfo.Contact),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,jobsInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,30,jobsInfo.Email),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,1500,jobsInfo.JobsContent),
                                      DbHelper.MakeInParam("@number",(DbType)SqlDbType.Int,int.MaxValue,jobsInfo.Number),
                                      DbHelper.MakeInParam("@paytreatment",(DbType)SqlDbType.NChar,15,jobsInfo.PayTreatment),                                     
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NVarChar,60,jobsInfo.Phone),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.Int,4,jobsInfo.Sex),
                                      DbHelper.MakeInParam("@welfare",(DbType)SqlDbType.NVarChar,50,jobsInfo.Welfare),
                                      DbHelper.MakeInParam("@workplace",(DbType)SqlDbType.NChar,40,jobsInfo.Workplace),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,jobsInfo.Audit),
                                      DbHelper.MakeInParam("@maturity",(DbType)SqlDbType.NVarChar,jobsInfo.Maturity.Length,jobsInfo.Maturity),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.NVarChar,jobsInfo.Published.Length,jobsInfo.Published),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,jobsInfo.Date.Length,jobsInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}jobs] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[companyname]=@companyname,[title]=@title,[address]=@address,[contact]=@contact,[education]=@education,[email]=@email,[content]=@content,[number]=@number,");
            commantText.Append("[paytreatment]=@paytreatment,[phone]=@phone,[sex]=@sex,[welfare]=@welfare,[workplace]=@workplace,[audit]=@audit,[published]=@published,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [jobsid]=@jobsid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        
        /// <summary>
        /// 根据jobsid更新人才招聘信息
        /// </summary>
        /// <param name="jobsInfo"></param>
        /// <returns></returns>
        public int UpdateJobsByJobsId(JobsInfo jobsInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsInfo.JobsId),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NChar,20,jobsInfo.JobsTitle),
                                      DbHelper.MakeInParam("@companyname",(DbType)SqlDbType.NChar,40,jobsInfo.CompanyName),
                                      DbHelper.MakeInParam("@address",(DbType)SqlDbType.NVarChar,100,jobsInfo.Address),
                                      DbHelper.MakeInParam("@contact",(DbType)SqlDbType.NChar,20,jobsInfo.Contact),
                                      DbHelper.MakeInParam("@education",(DbType)SqlDbType.NChar,10,jobsInfo.Education),
                                      DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,30,jobsInfo.Email),
                                      DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,1500,jobsInfo.JobsContent),
                                      DbHelper.MakeInParam("@number",(DbType)SqlDbType.Int,int.MaxValue,jobsInfo.Number),
                                      DbHelper.MakeInParam("@paytreatment",(DbType)SqlDbType.NChar,15,jobsInfo.PayTreatment),                                     
                                      DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NVarChar,60,jobsInfo.Phone),
                                      DbHelper.MakeInParam("@sex",(DbType)SqlDbType.Int,4,jobsInfo.Sex),
                                      DbHelper.MakeInParam("@welfare",(DbType)SqlDbType.NVarChar,50,jobsInfo.Welfare),
                                      DbHelper.MakeInParam("@workplace",(DbType)SqlDbType.NChar,40,jobsInfo.Workplace),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,jobsInfo.Audit),
                                      DbHelper.MakeInParam("@maturity",(DbType)SqlDbType.NVarChar,jobsInfo.Maturity.Length,jobsInfo.Maturity),
                                      DbHelper.MakeInParam("@published",(DbType)SqlDbType.NVarChar,jobsInfo.Published.Length,jobsInfo.Published),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.NChar,jobsInfo.Date.Length,jobsInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}jobs] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET[companyname]=@companyname,[title]=@title,[address]=@address,[contact]=@contact,[education]=@education,[email]=@email,[content]=@content,[number]=@number,");
            commantText.Append("[paytreatment]=@paytreatment,[phone]=@phone,[sex]=@sex,[welfare]=@welfare,[workplace]=@workplace,[audit]=@audit,[published]=@published,[maturity]=@maturity,[date]=@date");
            commantText.Append(" WHERE [jobsid]=@jobsid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据USERID和jobsid查找人才招聘信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="jobsid"></param>
        /// <returns></returns>
        public DataTable GetJobsByJobsIdAndUserId(string userid,string jobsid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}jobs] WHERE [userid]=@userid AND [jobsid]=@jobsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据jobsid查找人才招聘信息
        /// </summary>
        /// <param name="jobsid"></param>
        /// <returns></returns>
        public DataTable GetJobsByJobsId(string jobsid)
        {
            DbParameter parms = DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}jobs] WHERE [jobsid]=@jobsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有人才招聘数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetJobsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}jobs] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 根据USERID和jobsid删除人才招聘信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="jobsid"></param>
        /// <returns></returns>
        public int DelJobsByUserIdAndJobsId(string userid, string jobsid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}jobs] WHERE [userid]=@userid AND [jobsid]=@jobsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text,commandText,parms);
        }
        /// <summary>
        /// 根据jobsid删除人才招聘信息
        /// </summary>
        /// <param name="jobsid"></param>
        /// <returns></returns>
        public int DelJobsByJobsId(string jobsid)
        {
            DbParameter parms = DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsid);
            string commandText = string.Format("DELETE FROM [dbo].[{0}jobs] WHERE [jobsid]=@jobsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        /// <summary>
        /// 根据用户ID取得人才招聘总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountJobsByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(jobsid) FROM [dbo].[{0}jobs] WHERE [userid]=@userid",BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        /// <summary>
        /// 更新审核状态
        /// </summary>
        /// <param name="jobsid"></param>
        /// <param name="audit"></param>
        /// <returns></returns>
        public int UpDateJobsAuditByJobsId(string jobsid,bool audit)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@audit", (DbType)SqlDbType.Bit, 5, audit),
                                      DbHelper.MakeInParam("@jobsid",(DbType)SqlDbType.NVarChar,60,jobsid)
                                  };
            string commandText = string.Format("UPDATE [dbo].[{0}jobs] SET [audit]=@audit WHERE [jobsid]=@jobsid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
            
        }
        #endregion
        
    }
}